const connection = require("../app/database")

class MonmentService {
  async create(userId, content) {
    const statement = "INSERT INTO moment (user_id, content) VALUES (?,?);"
    const [res] = await connection.execute(statement, [userId, content])

    return res
  }

  // 查询动态列表函数
  // 设置默认值是为了防止用户什么都不传的情况下获取数据
  async queryList(limit = 10, offset = 0) {
    const statement = `SELECT 
    m.id id, m.content content, m.createAt createAt, m.updateAt updateAt,
    JSON_OBJECT('id',u.id, 'name',u.name) user,
    (SELECT COUNT(*) FROM comment WHERE comment.moment_id = m.id) commentCount,
    (SELECT COUNT(*) FROM moment_label ml WHERE ml.moment_id = m.id) labelCount
    FROM moment m
    LEFT JOIN user u ON u.id = m.user_id
    LIMIT ? OFFSET ?;`
    const [res] = await connection.execute(statement, [
      // 注意：这里不支持数字类型
      String(limit),
      String(offset),
    ])
    return res
  }

  // 查询动态详情
  async queryById(momentId) {
    const statement = `
    SELECT 
        m.id id, m.content content, m.createAt createTime, m.updateAt updateTime,
        JSON_OBJECT('id', u.id, 'name', u.name, 'avatarURL', u.avatar_url, 'createTime', u.createAt, 'updateTime', u.updateAt) user,
        (
          SELECT 
            JSON_ARRAYAGG(JSON_OBJECT(
              'id', c.id, 'content', c.content, 'commentId', c.comment_id,
              'user', JSON_OBJECT('id', cu.id, 'name', cu.name, 'avatarURL', u.avatar_url)
            ))
          FROM comment c
          LEFT JOIN user cu ON c.user_id = cu.id
          WHERE c.moment_id = m.id
        ) comments,
        (
          JSON_ARRAYAGG(JSON_OBJECT(
            'id', l.id, 'name', l.name
          ))
        ) labels
      FROM moment m
      LEFT JOIN user u ON u.id = m.user_id
      LEFT JOIN moment_label ml ON ml.moment_id = m.id
      LEFT JOIN label l ON ml.label_id = l.id
      WHERE m.id = ?
      GROUP BY m.id;`
    const [res] = await connection.execute(statement, [momentId])
    return res
  }

  // 修改动态详情
  async update(momentId, content) {
    const statement = `UPDATE moment SET content = ? WHERE id = ?;`
    const res = await connection.execute(statement, [content, momentId])
    return res
  }

  // 删除动态
  async remove(momentId) {
    const statement = `DELETE FROM moment where id = ?;`
    const res = await connection.execute(statement, [momentId])
    return res
  }

  // 数据库是否存在该关系
  async hasLabel(momentId, labelId) {
    const statement = `SELECT * FROM moment_label WHERE moment_id = ? AND label_id = ?;`
    const [result] = await connection.execute(statement, [
      String(momentId),
      labelId,
    ])
    // @ts-ignore
    return !!result.length
  }

  // 将关系插入关系表中
  async addLabel(momentId, labelId) {
    const statement = `INSERT INTO moment_label (moment_id, label_id) VALUES (?, ?);`
    const [result] = await connection.execute(statement, [momentId, labelId])
    return result
  }
}

module.exports = new MonmentService()
